CREATE OR REPLACE FUNCTION create_accessory_table(character varying) RETURNS VOID AS '
DECLARE 
  vFeatureName ALIAS FOR $1;
  curFeatureValues CURSOR (vfeatName CHARACTER VARYING) FOR 
      SELECT f.ft_character_value, d.doc_url
        FROM t_feature_key k, t_feature f, t_document d
        WHERE d.doc_lr_id = f.ft_entity_id 
            AND f.ft_key_id = k.fk_id
            AND k.fk_string = vfeatName
        ORDER BY 1;

  i INTEGER = 0;
  vValueCount INTEGER = 0;
  vcreateTableStmt CHARACTER VARYING(4000);
  vinsertStmt CHARACTER VARYING(4000);
  vinsertStmt2 CHARACTER VARYING(4000);
  vfeatureCharValue CHARACTER VARYING;
  vValue CHARACTER VARYING;
  vticketNumber CHARACTER VARYING;
  containsEqual INTEGER = 0;
  strc_number = CHARACTER VARYING(10);
  strc_count = CHARACTER VARYING(3);
  vDropLastColStmt CHARACTER VARYING;
BEGIN

    IF NOT EXISTS(SELECT f.ft_character_value
        FROM t_feature_key k, t_feature f, t_document d
        WHERE d.doc_lr_id = f.ft_entity_id 
            AND f.ft_key_id = k.fk_id
            AND k.fk_string = vFeatureName) THEN
        RAISE EXCEPTION ''Feature with name % does not exist.'', quote_literal(vFeatureName);
        EXIT;
    END IF;
--command to create the table - need to add one more column here
    vcreateTableStmt := ''CREATE TABLE '' || vFeatureName || '' (TicketNumber CHARACTER(10), '' || vFeatureName || '' CHARACTER VARYING(250), Strc_Count SMALLINT '';
    vcreateTableStmt := vcreateTableStmt || '' PRIMARY KEY(TicketNumber, '' || vFeatureName || '')) ;'';

    IF EXISTS(SELECT tablename FROM pg_tables WHERE LOWER(tablename) = LOWER(vFeatureName) ) THEN
        EXECUTE(''DROP TABLE '' || vFeatureName || '';'');
    END IF;
    EXECUTE vcreateTableStmt;

    OPEN curFeatureValues(vFeatureName);
    LOOP
        FETCH curFeatureValues INTO vfeatureCharValue, vticketNumber;
        IF  NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;
        IF vfeatureCharValue  IS NOT NULL AND vfeatureCharValue != '''' THEN

        vticketNumber := substring(vticketNumber from (char_length(vticketNumber) - 13) for 10);
        vinsertStmt := ''INSERT INTO '' || vFeatureName || '' (TicketNumber, '' || vFeatureName || '', Strc_count) VALUES ('';

        vValueCount := char_length(vfeatureCharValue) - char_length(replace(vfeatureCharValue, ''|'', '''')) + 1;
        i := 1;
        LOOP
            vValue := split_part(vfeatureCharValue, ''|'', i);
		   IF (vValue ~ '\d+=\d+') THEN
			 strc_number := split_part(vValue, ''='', 1);
			 strc_count := split_part(vValue, ''='', 2);
			 containsEqual := 1;
			 vinsertStmt2 := vinsertStmt || quote_literal(vticketNumber) || '', '' || quote_literal(strc_number) || '', '' || quote_literal(strc_count) || '');'';
		   ELSE
               vinsertStmt2 := vinsertStmt || quote_literal(vticketNumber) || '', '' || quote_literal(vValue) || '');'';
		   END IF;
            RAISE NOTICE ''%'', vinsertStmt2;
            EXECUTE vinsertStmt2;
            i := i + 1;
            IF i > vValueCount THEN
                EXIT;
            END IF;
        END LOOP;
        END IF;
    END LOOP;
    IF containsEqual = 0 THEN
	   vDropLastColStmt := ''ALTER TABLE '' || vFeatureName || '' DROP COLUMN Strc_Count ;'';
	   EXECUTE vDropLastColStmt;
	END IF;
    CLOSE curFeatureValues;
END;
' LANGUAGE 'plpgsql';

--add a record for each structure mentioned: ticket, structure name, times mentioned
--want to keep this script generic, and some features don't have the equal signs.
--add flag to see if the = sign is there
--if the flag is not used, then add an alter statement delete the column from the table
-- SELECT create_accessory_table('Addresses');
